package holo.Utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.fastjson.JSONObject;
import util.YtJSONObject;

import javax.sql.DataSource;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/**
 * @author Malegod_xiaofei
 * @create 2022-06-10-11:55
 */
public class Update_Holo_2018_1111 {
    //定义SQL 链接 预编译器
    private static DataSource dataSource = null;
    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;

    public static void main(String[] args) throws Exception {
        String[] month = {"20181111"};
        Map<String, JSONObject> check_region_id = new HashMap<>();
        check_region_id.put("130284", new YtJSONObject().put("new_region_id", "130223").put("province", "河北省").put("city", "唐山市").put("district", "滦州市").getHome());
        check_region_id.put("140211", new YtJSONObject().put("new_region_id", "140214").put("province", "山西省").put("city", "大同市").put("district", "云冈区").getHome());
        check_region_id.put("140404", new YtJSONObject().put("new_region_id", "140421").put("province", "山西省").put("city", "长治市").put("district", "上党区").getHome());
        check_region_id.put("140406", new YtJSONObject().put("new_region_id", "140481").put("province", "山西省").put("city", "长治市").put("district", "潞城市").getHome());
        check_region_id.put("232723", new YtJSONObject().put("new_region_id", "232701").put("province", "黑龙江省").put("city", "大兴安岭地区").put("district", "漠河市").getHome());
        check_region_id.put("370115", new YtJSONObject().put("new_region_id", "370125").put("province", "山东省").put("city", "济南市").put("district", "济阳区").getHome());
        check_region_id.put("441902", new YtJSONObject().put("new_region_id", "441931").put("province", "广东省").put("city", "东莞市").put("district", "南城街道").getHome());
        check_region_id.put("441904", new YtJSONObject().put("new_region_id", "441935").put("province", "广东省").put("city", "东莞市").put("district", "东城街道").getHome());
        check_region_id.put("441906", new YtJSONObject().put("new_region_id", "441917").put("province", "广东省").put("city", "东莞市").put("district", "万江街道").getHome());
        check_region_id.put("441911", new YtJSONObject().put("new_region_id", "441936").put("province", "广东省").put("city", "东莞市").put("district", "莞城街道").getHome());
        check_region_id.put("500156", new YtJSONObject().put("new_region_id", "500232").put("province", "重庆市").put("city", "重庆市").put("district", "武隆区").getHome());

        String sql = "";
        for (int i = 0; i < month.length; i++) {

            for (Map.Entry<String, JSONObject> s : check_region_id.entrySet()) {
                System.out.println("月份 : " + month[i]);
                sql = "update dbadmin.o2o_region_category_app_tui_" + month[i] + " set region_id = '" + s.getValue().getString("new_region_id") + "',province = '" + s.getValue().getString("province") + "',city = '" + s.getValue().getString("city") + "',district = '" + s.getValue().getString("district") + "' where region_id = '" + s.getKey() + "';";
                System.out.println("更新 sql :" + sql);
                update(sql);
                sql = "update dbadmin.o2o_showlive_app_" + month[i] + " set regional_id = '" + s.getValue().getString("new_region_id") + "',province = '" + s.getValue().getString("province") + "',city = '" + s.getValue().getString("city") + "',district = '" + s.getValue().getString("district") + "' where regional_id = '" + s.getKey() + "';";
                update(sql);
                System.out.println("更新 sql :" + sql);
                sql = "update dbadmin.o2o_tourism_idl_tui_" + month[i] + " set region_id_out = '" + s.getValue().getString("new_region_id") + "',province_out = '" + s.getValue().getString("province") + "',city_out = '" + s.getValue().getString("city") + "',district_out = '" + s.getValue().getString("district") + "' where region_id_out = '" + s.getKey() + "';";
                update(sql);
                System.out.println("更新 sql :" + sql);
                sql = "update dbadmin.o2o_allplat_shop_bdl_" + month[i] + " set region_id = '" + s.getValue().getString("new_region_id") + "' where region_id = '" + s.getKey() + "';";
                update(sql);
                System.out.println("更新 sql :" + sql);
            }
        }

    }

    public static void select(String sql) throws SQLException {

        //获取连接
        Properties properties = new Properties();
        try {
            properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("gov/pgconnect.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }

        // 获取德鲁伊对象连接器
        if (connection == null) {
            try {
                connection = dataSource.getConnection();
                System.out.println("connection : -->" + connection);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
//        String executoresultSetQL = "select * from dim_data.dim_platform";

        preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        // 打印 resultSet 数据
        ResultSetMetaData resultSetmd = resultSet.getMetaData();
        int columnsNumber = resultSetmd.getColumnCount();
        while (resultSet.next()) {
            for (int i = 1; i <= columnsNumber; i++) {
                if (i > 1) System.out.print(",  ");
                String columnValue = resultSet.getString(i);
                System.out.print(columnValue + " " + resultSetmd.getColumnName(i));
            }
            System.out.println("");
        }
        preparedStatement.close();
    }

    public static void update(String sql) throws SQLException {

        //获取连接
        Properties properties = new Properties();
        try {
            properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("gov/pgconnect.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }

        // 获取德鲁伊对象连接器
        if (connection == null) {
            try {
                connection = dataSource.getConnection();
                System.out.println("connection : -->" + connection);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
//        String executoresultSetQL = "select * from dim_data.dim_platform";

        preparedStatement = connection.prepareStatement(sql);
        int resultSet = preparedStatement.executeUpdate();
        // 打印 resultSet 数据
        System.out.println("一共 : " + resultSet + " 条数据受影响 ! ");
        preparedStatement.close();

    }


}